﻿CREATE PROCEDURE [dbo].[Ebook_Item_Get]
	@userId UNIQUEIDENTIFIER,
	@hashTag NVARCHAR(450)
AS
BEGIN
	SET @hashTag = LOWER(@hashTag)

	DECLARE @bookId UNIQUEIDENTIFIER

	SELECT @bookId = bt.[BookId]
	FROM [dbo].[text_BookTree] AS bt
	INNER JOIN [dbo].[text_BookTree_Cache] AS c ON c.BookTreeId = bt.BookTreeId
	INNER JOIN [dbo].[ebook_Item] AS eb ON eb.[BookId] = bt.[BookId]
	WHERE c.[Url] = @hashTag AND eb.[Enable] = 1

	IF @bookId IS NULL
	BEGIN
		RETURN
	END

	DECLARE @ebookUrl NVARCHAR(1024), @cost INT, @purchaseText NVARCHAR(MAX)

	SELECT @ebookUrl = eb.[InnerPath]
	FROM [dbo].[purchase_Item] AS i 
	INNER JOIN [dbo].[purchase_Status] AS st ON st.[StatusId] = i.[PurchaseStatusId]
	INNER JOIN [dbo].[ebook_Item] AS eb ON eb.[BookId] = i.[BookId]
	WHERE st.[Direction] > 0 AND i.[BookId] = @bookId AND i.[UserId] = @userId AND eb.[Enable] = 1

	SELECT @cost = eb.[TotalCost], @purchaseText = eb.[Text]
	FROM [dbo].[ebook_Item] AS eb 
	WHERE eb.[BookId] = @bookId

	SELECT @ebookUrl AS [EbookUrl], @cost AS [Cost], @bookId AS [BookId], @purchaseText AS [PurchaseText]

	--IF @ebookUrl IS NOT NULL
	--BEGIN
	--	RETURN
	--END

	;WITH b AS (
		SELECT b.[BookId]
					,[Author]
					,[CoverUrl]
					,bt.[HashTag]
					,bt.[Text]
		FROM [dbo].[text_Book] AS b
		INNER JOIN [dbo].[ebook_Item] AS eb ON eb.[BookId] = b.[BookId]
		INNER JOIN [dbo].[text_BookTree] AS bt ON bt.[BookId] = b.[BookId]
		WHERE eb.[BookId] <> @bookId AND eb.[Enable] = 1 AND bt.[LeftKey] = 1

		UNION ALL

		SELECT b.[BookId]
					,[Author]
					,[CoverUrl]
					,bt.[HashTag]
					,bt.[Text]
		FROM [dbo].[text_Book] AS b
		INNER JOIN [dbo].[ebook_Item] AS eb ON eb.[BookId] = b.[BookId]
		INNER JOIN [dbo].[text_BookTree] AS bt ON bt.[BookId] = b.[BookId]
		WHERE eb.[BookId] = @bookId AND eb.[Enable] = 1 AND bt.[LeftKey] = 1 
	)
	SELECT [BookId]
				,[Author]
				,[CoverUrl]
				,[HashTag]
				,[Text]
	FROM b
	ORDER BY [BookId]

	SELECT [Text]
	FROM [dbo].[text_BookTree]
	WHERE [BookId] = @bookId AND [ParentBookTreeId] = (SELECT [BookTreeId] FROM [dbo].[text_BookTree] WHERE [BookId] = @bookId AND [LeftKey] = 1) AND [IsHead] = 0
	ORDER BY [LeftKey]

END